wanghai

v$open_cursor是oracle提供的一张查看每个session打开过的cursor的视图,它的表结构如下
SQL> desc v$open_cursor
Name                                                  Null?    Type
----------------------------------------------------- -------- ------------------------------------
SADDR                                                          RAW(4)
SID                                                            NUMBER
USER_NAME                                                      VARCHAR2(30)
ADDRESS                                                        RAW(4)
HASH_VALUE                                                     NUMBER
SQL_TEXT                                                       VARCHAR2(60)

当我们执行一条sql语句的时候,我们将会在shared pool产生一个library cache object,cursor就是其中针对于sql语句的一种library cache object.另外我们会在pga有一个cursor的拷贝,同时在客户端会有一个statement handle,这些都被称为cursor,在v$open_cursor里面我们可以看到当前打开的cursor和pga内cached cursor.

session_cached_cursor
这个参数限制了在pga内session cursor cache list的长度,session cursor cache list是一条双向的lru链表,当一个session打算关闭一个cursor时,如果这个cursor的parse count超过3次,那么这个cursor将会被加到session cursor cache list的MRU端.当一个session打算parse一个sql时,它会先去pga内搜索session cursor cache list,如果找到那么会把这个cursor脱离list,然后当关闭的时候再把这个cursor加到MRU端.session_cached_cursor提供了快速软分析的功能,提供了比soft parse更高的性能.

下面做个实验来证明以上这些理论

SQL> select distinct sid from v$mystat;

       SID
----------
        12


SQL> show parameter session_cached_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors               integer     0

这是设置session_cached_cursors为0,禁止了cache cursor的功能

我们另开一个sqlplus来看v$open_cursor

SQL> select * from v$open_cursor where sid=12;

SADDR           SID USER_NAME                      ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------ -------- ---------- ------------------------------------------------------------
95D5AFCC         12 TAOBAO                         99DA7F20  745629022 select distinct sid from v$mystat

能在v$open_cursor里看到这条sql

执行下一条语句
SQL> select owner from test where rownum=1;

OWNER
------------------------------
SYS

在来看v$open_cursor有什么变化

SQL> /

SADDR           SID USER_NAME                      ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------ -------- ---------- ------------------------------------------------------------
95D5AFCC         12 TAOBAO                         99C9AEF8  608211958 select owner from test where rownum=1

可以看到语句变了,来看看current open cursor和session cached cursor的数目
SQL>  select sid,n.name,s.value  used
  2   from
  3     sys.v_$statname  n,
  4     sys.v_$sesstat  s
  5   where
  6     n.name in ('opened cursors current', 'session cursor cache count') and
  7     s.statistic# = n.statistic#
  8     and sid=12;

       SID NAME                                                                   USED
---------- ---------------------------------------------------------------- ----------
        12 opened cursors current                                                    1
        12 session cursor cache count                                                0

我们来把session_cached_cursors修改成1
SQL> alter session set session_cached_cursors=1;

Session altered.

再执行上面的语句

SQL> select owner from test where rownum=1;

OWNER
------------------------------
SYS

看看v$open_cursor

SQL> select * from v$open_cursor where sid=12;

SADDR           SID USER_NAME                      ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------ -------- ---------- ------------------------------------------------------------
95D5AFCC         12 TAOBAO                         99C9AEF8  608211958 select owner from test where rownum=1



此处执行3次 select * from v$open_cursor where sid=12,oracle进行了3次soft parse
SQL> select owner from test where rownum=1;

OWNER
------------------------------
SYS
SQL> select owner from test where rownum=1;

OWNER
------------------------------
SYS

然后再执行

SQL> select distinct sid from v$mystat;

       SID
----------
        12

再来看看v$open_cursor有什么变化
SQL> /

SADDR           SID USER_NAME                      ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------ -------- ---------- ------------------------------------------------------------
95D5AFCC         12 TAOBAO                         99C9AEF8  608211958 select owner from test where rownum=1
95D5AFCC         12 TAOBAO                         99DA7F20  745629022 select distinct sid from v$mystat

v$open_cursor有了两条记录,再看一下current open cursor和session cached cursor的数目

SQL>  select sid,n.name,s.value  used
  2   from
  3     sys.v_$statname  n,
  4     sys.v_$sesstat  s
  5   where
  6     n.name in ('opened cursors current', 'session cursor cache count') and
  7     s.statistic# = n.statistic#
  8     and sid=12;

       SID NAME                                                                   USED
---------- ---------------------------------------------------------------- ----------
        12 opened cursors current                                                    1
        12 session cursor cache count                                                1
        
果然有一句sql进了session cursor cache,"select owner from test where rownum=1"这个sql相关的cursor就被cache起来了.

再执行一条新的语句
SQL> select owner from test where rownum<2;

OWNER
------------------------------
SYS

看一下v$open_cursor
SQL> select * from v$open_cursor where sid=12;

SADDR           SID USER_NAME                      ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------ -------- ---------- ------------------------------------------------------------
95D5AFCC         12 TAOBAO                         99CA4FFC 2584499260 select owner from test where rownum<2
95D5AFCC         12 TAOBAO                         99C9AEF8  608211958 select owner from test where rownum=1

可以看到上一次的"select distinct sid from v$mystat"已经被关闭,在v$open_cursor中不可见,但是cached cursor "select owner from test where rownum=1"还能看到

这时候运行3次select distinct sid from v$mystat
SQL> select distinct sid from v$mystat;

       SID
----------
        12
SQL> select distinct sid from v$mystat;

       SID
----------
        12
SQL> select distinct sid from v$mystat;

       SID
----------
        12
        
然后再运行select owner from test where rownum<2

SQL>  select owner from test where rownum<2;

OWNER
------------------------------
SYS


再去查看v$open_cursor

SQL> select * from v$open_cursor where sid=12;

SADDR           SID USER_NAME                      ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------ -------- ---------- ------------------------------------------------------------
95D5AFCC         12 TAOBAO                         99C6C628 1755089113 select owner from test where rownum<2
95D5AFCC         12 TAOBAO                         99DA7F20  745629022 select distinct sid from v$mystat

发现原来cached cursor"select owner from test where rownum=1"已经被替换出了session cursor cache,而变成了"select distinct sid from v$mystat"

我们再修改session_cached_cursors

alter session set session_cached_cursors=2;

再重复一下上面的过程,再去看v$open_cursor

SQL> /

SADDR           SID USER_NAME                      ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------ -------- ---------- ------------------------------------------------------------
95D5AFCC         12 TAOBAO                         99CA4FFC 2584499260 select owner from test where rownum<2
95D5AFCC         12 TAOBAO                         99C9AEF8  608211958 select owner from test where rownum=1
95D5AFCC         12 TAOBAO                         99DA7F20  745629022 select distinct sid from v$mystat

可以看到现在有3个cursor,看一下current open cursor和session cached cursor的数目


SQL>  select sid,n.name,s.value  used
  2   from
  3     sys.v_$statname  n,
  4     sys.v_$sesstat  s
  5   where
  6     n.name in ('opened cursors current', 'session cursor cache count') and
  7     s.statistic# = n.statistic#
  8     and sid=12;

       SID NAME                                                                   USED
---------- ---------------------------------------------------------------- ----------
        12 opened cursors current                                                    1
        12 session cursor cache count                                                2
        
这下有2个cached cursor,一个current open cursor.

从这个实验我们可以清楚得看到v$open_cursor视图和session_cached_cursors参数的作用.

fly115

兄弟，好久没灌水了

ern

学习学习，这几天正在琢磨Ora-01000

jametong

引用:

Originally posted by ern at 2005-7-28 22:31:
学习学习，这几天正在琢磨Ora-01000

大部分都是由于应用没有合理的释放cursor资源造成的,,

特别是在java应用中,,, 其他情况下出现这个错误的机会是非常小的..

abei_hero

session cursor cache 在session内部产生大量重复执行SQL时，将soft parse转为fast soft parse，对提高OLTP类系统的响应时间和可扩展性有很大好处

wanghai

由于java端配置有自己的cache cursor size,对于weblogic来说,当一个java session打开一个cursor时它会被加到cache cursor list MRU端,同时把LRU端的一个cursor脱离list,而且list里面的Node对象也被标记了是否最近被访问过,如果这个curor需要被移出list的时候它最近被访问过的话那将会把它从LRU移到MRU,同时去LRU端寻找下一个最近没有被访问过cursor移出list.java的cache cursor list和oracle的session cached cursor list采用了相似的机制.通常如果设置java端的cache cursor size>=open_cursors就会出现
Ora-01000,所以说当设置oracle端的open_cursors时要和web服务器端的设置一起考虑.

yyt168

这个参数是挺重要的

jametong

对于java应用, 建议加大open_cursor参数的设置,

pjm_ocp

我单位数据库session_cached_cursors经常报ORA-04031 我把SHARE_POOL_SIZE 从350M 调到了500M 系统还是偶尔报,
我可以通过使用session_cached_cursors 来避免这错误吗

lfree

学习。

brucewoo

不错

xue_lei

不得不佩服wanghai

mget

学习学习！

hobby2273

高手就是高手！！！

hobby2273

还要请问一下，什么时候才需要设置session_cached_cursors这个参数？？？

jametong

引用:

原帖由 hobby2273 于 2006-8-1 11:12 发表
还要请问一下，什么时候才需要设置session_cached_cursors这个参数？？？

对于web应用, 建议使用..

zyong

引用:

原帖由 hobby2273 于 2006-8-1 11:12 发表
还要请问一下，什么时候才需要设置session_cached_cursors这个参数？？？

Matelink 上有講到﹐在 Oracle Forms 應用時 Forms頻繁地打開和關閉時 session_cached_cursor尤其有用。所以這個時候設置  session_cached_cursor 是很有必要的。



w hai 的演示過程已以很明確地說到 sql 被緩存的現象。

但我有疑問﹐這個 sql 必面連續地執行 3 次﹐soft parse 三次﹐也就是說中間不能有間接﹐那樣的話這條 sql 才能有可能被 cache 。

我測試如下﹕
select count(*) from tab;

select count(*) from tab;

select count(*) from dual;

select count(*) from tab;


上面講來﹐select count(*) from tab; 這條 sql 也有被 parse 3 次﹐但由于中間有執行 select count(*) from dual; 而
這條 sql 卻不能被 cache 。

所以 這樣的 話一般 sql 很難被 cache ﹐像在 pl/sql 中的 語句 似乎就不可能.....

wanghai

打个比方，select count(*) from tab;不是一个session在执行，在生产环境里面可能有很多session都在执行它，所以它很容易就达到3次,在这一刻，他不是一个人,不是一个人！！！另外library cache object保存了sql parse的次数，所以你这种情况一样可以cache住。

SQL> select * from v$open_cursor where sid=74;

SADDR           SID USER_NAME                      ADDRESS  HASH_VALUE
-------- ---------- ------------------------------ -------- ----------
SQL_TEXT
------------------------------------------------------------
69DA1C80         74 TAOBAO                         732F7248 4157546369
select * from v$open_cursor where sid=74


SQL> alter session set session_cached_cursors=1;

Session altered.

SQL> select count(*) from tab;

  COUNT(*)
----------
        84

SQL> select count(*) from tab;

  COUNT(*)
----------
        84

SQL> select * from dual;

D
-
X

SQL> select count(*) from tab;

  COUNT(*)
----------
        84

SQL> select * from v$open_cursor where sid=74;

SADDR           SID USER_NAME                      ADDRESS  HASH_VALUE
-------- ---------- ------------------------------ -------- ----------
SQL_TEXT
------------------------------------------------------------
69DA1C80         74 TAOBAO                         75DE8558 1431850959
select count(*) from tab

69DA1C80         74 TAOBAO                         732F7248 4157546369
select * from v$open_cursor where sid=74

zyong

引用:

原帖由 wanghai 于 2006-8-4 10:31 发表
打个比方，select count(*) from tab;不是一个session在执行，在生产环境里面可能有很多session都在执行它，所以它很容易就达到3次,在这一刻，他不是一个人,不是一个人！！！另外library cache object保存了sql p ...

謝謝 wanghai ,,,,不好意思﹐還是有下面的疑問...

Q1﹑
這樣理解你的意思﹕

在生產環境中﹐如果某同樣的 username 在不同的 session 中有執行這條語句﹐那么這個 parse count 是這几個session 的總計﹐對嗎﹖﹐當session 再次解析這條 sql 時發現 已經達到了 3 次﹐那么會將它列入 cache mru 列表﹐也會將sql cache 。


Q2﹐v$sqlarea 中的 PARSE_CALLS 欄位能將 parse 的次數體現出來嗎﹖如果是﹐那 即便存 cache mru list 列表﹐在對 sql 作 softer soft parse 時也還是當成一次解析記錄下來﹐對嗎﹖


Q3﹑可否能找出這個會話最近有執行過的 sql ﹐除了用 logmine ,audit外... 。

wanghai

1.it's ok
2.会增加parse call，你可以做个测试
3.可以从v$open_cursor里面找到一些，但是只是针对被cache住的，如果没有进cache的话恐怕无法找回